set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nonstrict;
set hive.exec.max.dynamic.partitions=30;
set hive.exec.max.dynamic.partitions.pernode=30;


with -- 网点信息
     network as (
        select id   as network_id,
                name as network_name, --    string    名称
                code as network_code, --    string    编号
                country_id,           --    bigint    所属国家
                country_code,         --    string    所属国家编码
                country_desc,         --    string    所属国家名称
                country_three_code,   --    string    所属国家三字码
                regional_id,          --    bigint    大区ID
                regional_desc,        --    string    大区名称
                -- manage_regional_code, -- 管理大区code
                -- manage_regional_name, -- 管理大区name
                financial_center_id,  -- 代理区id
                financial_center_code,--    stirng    所属代理区code
                financial_center_desc,-- 代理区name
                provider_id,          --    bigint    省份ID
                provider_desc,        --    string    省份
                city_id,              --    bigint    城市ID
                city_desc,            --    string    城市
                area_id,              --    bigint    区/县ID
                area_desc,            --    string    区/县
                network_type,            --    string    网点类型描述
                is_enable,            --    int    '是否启用:1启用    2不启用'
                is_delete,            --    int    '是否删除:1未删除    2已删除'
                is_receiving,         --    int    '是否有收货电子围栏:1是    2否'
                is_dispatch           --    int    '是否有派货电子围栏:1是    2否'
         from jms_dim.dim_network_whole_massage
     ),

     -- 区域码表：区县-市-省
     area as (
         select id as code, cn_name
         from jms_dim.dim_lmdm_sys_area
         where type = 4
     ),
     city as (
         select id as code, cn_name
         from jms_dim.dim_lmdm_sys_area
         where type = 3
     ),
     province as (
         select id as code, cn_name
         from jms_dim.dim_lmdm_sys_area
         where type = 2
     ),

     customer as (
         SELECT t1.code                    as customer_code,
                t1.name                    as customer_name,
                t1.mall_id,
                t1.mall_name,
                t1.network_id              as customer_belong_network_id, -- 客户所属网点
                network.network_name       as customer_belong_network_name,
                t1.abbreviation            as customer_abbreviation,      -- string  comment '客户简称',
                t1.industry_id             as customer_industry_id,       --    bigint comment    '行业类型',
                t1.type_id                 as customer_type_id,           --    bigint comment    '客户类型',
                case t1.type_id
                    when 36 then '商务间客户(企业)'
                    when 37 then '电商件客户(个人)'
                    when 38 then '个人件客户'
                    else '' end            as customer_type_name,
                t1.country_id              as customer_country_id,        --   '国家ID',
                t1.country_code            as customer_country_code,      --   '国家代码',
                t1.province_id             as customer_province_id,       --   '省份ID',
                t1.city_id                 as customer_city_id,           --   '城市ID',
                t1.area_id                 as customer_area_id,           --   '区域ID',
                province.cn_name           as province_desc,
                city.cn_name               as city_desc,
                area.cn_name               as area_desc,
                t1.address                 as customer_address,           --  '地址',
                t1.is_enable,                                             --  '是否启用:1启用    2不启用',
                t1.nature_id,                                             --  '客户性质:国有、民营、股份制、外资、社会组织机构',
                case nature_id
                    when 300 then '国有'
                    when 301 then '民营'
                    when 302 then '股份制'
                    when 303 then '外资'
                    when 304 then '社会组织机构'
                    when 305 then '个人'
                    else ''
                    end                    as nature_id_name,
                t1.customer_level_id,                                     --  '客户等级：VIP、普通',
                t1.is_vip_account,                                        --  '是否开通VIP系统账户:1-是， 2-否',
                t1.is_open_cod,
                t1.settlement_id,                                         --  '结算方式：寄付月结、寄付现结',
                case settlement_id
                    when 319 then '寄付月结'
                    when 320 then '寄付现结'
                    when 321 then '到付现结'
                    else '' end            as settlement_name,            -- 结算状态名
                customer_share_id,                                        --  '共享客户：全网共享、下级共享、不共享',
                pay_id,                                                   --  '付款方式：现金、转账、支票',
                case pay_id
                    when 126 then '微信'
                    when 131 then '扫码付'
                    when 130 then '招行聚合支付'
                    when 127 then '支付宝'
                    when 129 then '支票'
                    when 124 then '现金'
                    when 125 then '银联支付'
                    when 128 then '预付款'
                    else ''
                    end                    as pay_type_name,
                source_id,                                                --    bigint comment    '客户来源',
                meter_bubble,                                             --    bigint comment    '计泡参数',
                quote_id,                                                 --    bigint comment    '客户报价方式：标准报价、定制报价',
                --46-标准报价,47-定制报价
                case quote_id
                    when 46 then '标准报价'
                    when 47 then '定制报价'
                    else ''
                    end                    as quote_name,
                account_period_id,                                        --    bigint comment    '账期类型：月结、周结',
                case account_period_id
                    when 315 then '月结'
                    when 316 then '周结'
                    else ''
                    end                    as account_period_name,
                attribute_id,                                             -- '客户属性 普通客户（默认）、农特市场客户、专业市场客户、总部营销推广活动客户',
                case attribute_id
                    when 343 then '普通客户'
                    when 344 then '农特客户'
                    when 345 then '专业市场客户'
                    when 347 then '项目大客户'
                    when 348 then '共享客户（寄件平台类）'
                    when 349 then '共享客户（门店调拨类）'
                    when 350 then '网点散客共用客户'
                    when 351 then '自寄服务点客户'
                    else ''
                    end                    as customer_attribute_name,
                recalc_cost_flag,                                         -- '是否按照揽重重算费用(1:是    2:否)',
                category_primary_id,                                      -- '主营类目ID',
                category_primary_name,                                    -- '主营类目名称',
                substr(create_time, 1, 19) as create_time
         FROM jms_dim.dim_lmdm_sys_customer t1
                  left join
              network
              on t1.network_id = network.network_id
                  left join
              area
              on t1.area_id = area.code
                  left join
              city
              on t1.city_id = city.code
                  left join
              province
              on t1.province_id = province.code
         where t1.is_delete <> 2
     ),
     -- 异常件编码维表
     abnormal as (
         select name, --    string    异常名称
                code  --    string    异常编码
         from jms_dim.dim_lmdm_sys_abnormal_piece
     ),
     -- 订单
     orders as (
--订单是根据 update_time 进行分区
         select orders.waybill_id,
                orders.order_source_code,
                orders.order_source_name,
                orders.customer_code,
                substr(pick_time, 1, 19)           as pick_time,
                substr(customer_order_time, 1, 19) as customer_order_time, --    timestamp    客户下单时间
                substr(input_time, 1, 19)          as order_input_time,    --    timestamp    订单录入时间
                substr(cancel_time, 1, 19)         as cancel_time,         --    timestamp    订单取消时间
                real_pick_network_code,                                    --    string    实际取件网点code
                order_status_code,                                         --    bigint    订单状态code
                case order_status_code
                    when 100 then '未调派'
                    when 106 then '已调派代理区'
                    when 101 then '已调派网点'
                    when 102 then '已调派业务员'
                    when 103 then '已取件'
                    when 104 then '已取消'
                    when 105 then '取件失败'
                    else ''
                    end                            as order_status_name,   --
                goods_type_code,
                goods_name
         from jms_dwd.dwd_yl_oms_oms_order_dt orders
                  inner join customer
                             on orders.customer_code = customer.customer_code
         where dt <= '{{ execution_date | cst_ds }}'
           and dt >= date_add('{{ execution_date | cst_ds }}', -14)
     ),
     -- 运单
     waybill as (
         select a.waybill_no
              , a.waybill_status_code
              , a.order_source_code
              , a.ordersource_code
              , a.ordersource_name
              , a.waybill_status_name
              , a.goods_type_code
              , a.goods_type_name
              , a.package_total_volume
              , a.package_volume
              , a.package_charge_weight
              , a.package_total_weight
              , a.package_inside_charge_weight
              , a.collect_time
              , a.dispatch_time
              , a.dispatch_network_code
              , a.dispatch_network_name
              , a.exception_code
              , a.terminal_dispatch_code
              , a.is_sign
              , a.sign_time
              , a.is_abnormal
              , a.abnormal_reg_network_code
              , a.abnormal_reg_time
              , a.is_void
              , a.is_refund
              , a.customer_code
              , a.customer_name
              , a.sender_province_id
              , net1.provider_desc as sender_province_desc
              , a.sender_city_id
              , net1.area_desc     as sender_area_desc
              , a.sender_area_id
              , net1.city_desc     as sender_city_desc
              , a.pick_network_code
              , a.pick_network_name
              , a.waybill_source_code
              , a.waybill_source_name
              , a.freight
              , a.total_freight
              , a.receiver_province_id
              , net2.provider_desc as receiver_province_desc
              , a.receiver_city_id
              , net2.city_desc     as receiver_city_desc
              , a.receiver_area_id
              , net2.area_desc     as receiver_area_desc
              , a.is_business
              , a.is_receive
              , a.dt
         from (
                  select waybill_no,                                            -- string comment     '运单号',
                         waybill_status_code,                                   -- bigint comment     '运单状态',
                         order_source_code,
                         ordersource_code,
                         ordersource_name,
                         case waybill_status_code
                             when 201 then '已揽件'
                             when 203 then '运送中'
                             when 204 then '派件中'
                             when 205 then '已签收'
                             when 206 then '退件中'
                             when 207 then '已退件'
                             when 208 then '已拒收'
                             else ''
                             end                          as waybill_status_name,
                         goods_type_code,                                       --     string comment     '物品类型code',
                         goods_type_name,
                         package_total_volume,                                  -- decimal(14,2)    comment '包裹总体积:单位立方厘米',
                         package_volume,
                         package_charge_weight,                                 -- decimal(14,2)    comment '包裹计费重量:单位千克',
                         package_total_weight,                                  --  decimal(14,2)    comment '包裹总重量:单位千克',
                         package_inside_charge_weight,                          --  decimal(14,2)    comment '内部计费总重量:单位千克',
                         substr(collect_time, 1, 19)      as collect_time,      --     timestamp comment     '揽件时间',
                         substr(dispatch_time, 1, 19)     as dispatch_time,     --     timestamp comment     '派件时间',
                         dispatch_network_code,                                 --     string comment     '派件网点',
                         dispatch_network_name,                                 --     string comment     '派件网点名称',
                         exception_code,                                        --     string comment     '异常原因编码',
                         terminal_dispatch_code,                                --    string comment    '三段码',
                         is_sign,                                               --    smallint comment    '签收标识    1是，0否',
                         substr(sign_time, 1, 19)         as sign_time,         --    timestamp comment    '签收时间',
                         is_abnormal,                                           --    smallint comment    '问题件标识    1是，0否',
                         abnormal_reg_network_code,                             --    string comment    '问题件登记网点code',
                         substr(abnormal_reg_time, 1, 19) as abnormal_reg_time, --    timestamp comment    '问题件登记时间',
                         is_void,                                               --    smallint comment    '是否作废件1是0否',
                         is_refund,                                             --    smallint comment    '是否退件1是0否',
                         customer_code,                                         --    string comment    '客户编号code',
                         customer_name,                                         --    string comment    '客户编号名称',
                         sender_province_id,                                    --    bigint comment    '寄件省份id',
                         sender_city_id,                                        --    bigint comment    '寄件城市id',
                         sender_area_id,                                        --    bigint comment    '寄件区域Id',
                         pick_network_code,
                         pick_network_name,
                         waybill_source_code,                                   --    string    运单来源code
                         waybill_source_name,                                   --    string    运单来源名称
                         freight,                                               --    decimal(14,2)    comment '运费',
                         total_freight,                                         --    decimal(14,2)    comment '总运费',
                         receiver_province_id,                                  --    bigint    comment '收件省份id',
                         receiver_city_id,                                      --    bigint    comment '收件城市id',
                         receiver_area_id,                                      --    bigint    comment '收件区域id',
                         is_business,                                           --    smallint    comment '是否是商务件0否，1是',
                         is_receive,                                            --    smallint comment '是否已收款    1是，0否',
                         dt
                  from jms_dwd.dwd_yl_oms_oms_waybill_incre_dt
                  where dt <= '{{ execution_date | cst_ds }}' -- 取最近30天的数据
                    and dt >= date_add('{{ execution_date | cst_ds }}', -14)
                    and customer_code is not null
              ) a
                  inner join customer on a.customer_code = customer.customer_code
                  left join
              network net1
              on a.pick_network_code = net1.network_code
                  left join
              network net2
              on a.dispatch_network_code = net2.network_code
     ),
     -- 揽收
     taking as (
         select t.*, network.network_name
         from (select billcode                      as waybill_no,      -- 运单编号
                      substr(scantime_early, 1, 19) as scantime_taking, --    timestamp comment    '扫描时间',
                      scansitecode_early            as scansitecode_taking
               from jms_dwd.dwd_barscan_taking_dt
               where dt <= '{{ execution_date | cst_ds }}' -- 取最近30天的数据
                 and dt >= date_add('{{ execution_date | cst_ds }}', -14)
                 and date_format(scantime_early, 'yyyy-MM-dd') >= date_add('{{ execution_date | cst_ds }}', -14)
                 and date_format(scantime_early, 'yyyy-MM-dd') <= date_add('{{ execution_date | cst_ds }}', 0)
                 and dimtype = 'billcode') t
                  left join network
                            on t.scansitecode_taking = network.network_code
     ),

     warehouse as (
         select t.*, network.network_name
         from (
                  select billcode                      as waybill_no,             --    string    运单编号
                         scansitecode_early            as scansitecode_warehouse, --    string    扫描网点编号
                         substr(scantime_early, 1, 19) as scantime_warehouse      --    timestamp    扫描时间
                  from jms_dwd.dwd_barscan_warehousing_dt
                  where dt <= '{{ execution_date | cst_ds }}' -- 取最近30天的数据
                    and dt >= date_add('{{ execution_date | cst_ds }}', -14)
                    and date_format(scantime_early, 'yyyy-MM-dd') <= date_add('{{ execution_date | cst_ds }}', 0)
                    and date_format(scantime_early, 'yyyy-MM-dd') >= date_add('{{ execution_date | cst_ds }}', -14)
                    and dimtype = 'billcode'
              ) t
                  left join
              network
              on t.scansitecode_warehouse = network.network_code
     ),
     -- 发件
     send as (
         select waybill_no,
                scantime_send,
                inputtime_send,
                scansitecode_send
         from (
                  select billcode                       as                                     waybill_no,--    string comment    '运单编号',
                         substr(scantime_early, 1, 19)  as                                     scantime_send, --    timestamp comment    '扫描时间',
                         substr(inputtime_early, 1, 19) as                                     inputtime_send, --    timestamp comment    '录入时间',
                         scansitecode_early             as                                     scansitecode_send,
                         ROW_NUMBER() over (partition by billcode order by scantime_early asc) rk
                  from jms_dwd.dwd_barscan_send_dt
                  where dt <= '{{ execution_date | cst_ds }}' -- 取最近30天的数据
                    and dt >= date_add('{{ execution_date | cst_ds }}', -14)
                    and date_format(scantime_early, 'yyyy-MM-dd') <= date_add('{{ execution_date | cst_ds }}', 0)
                    and date_format(scantime_early, 'yyyy-MM-dd') >= date_add('{{ execution_date | cst_ds }}', -14)
              ) t
         where rk = 1
     ),
     -- 派件
     deliver as ( -- 派件时间最早
         select billcode                       as waybill_no,       --    string comment    '运单编号',
                scansitecode_early             as scansitecode_deliver,
                substr(scantime_early, 1, 19)  as scantime_deliver,--    timestamp comment    '扫描时间', 派件时间
                substr(inputtime_early, 1, 19) as inputtime_deliver --    timestamp comment    '录入时间',
         from jms_dwd.dwd_barscan_deliver_dt
         where dt <= '{{ execution_date | cst_ds }}' -- 取最近30天的数据
           and dt >= date_add('{{ execution_date | cst_ds }}', -14)
           and date_format(scantime_early, 'yyyy-MM-dd') <= date_add('{{ execution_date | cst_ds }}', 0)
           and date_format(scantime_early, 'yyyy-MM-dd') >= date_add('{{ execution_date | cst_ds }}', -14)
           and dimtype = 'billcode'
     ),
     -- 代收
     collect as (
         select waybill_no--,scansitecode_later
              , min(case
                        when scantype_later_collect = '入库扫描'
                        then nvl(split(scantime_early_collect, ',')[1],split(scantime_later_collect, ',')[1])
                        else null end) as instorage_scansitecode
              , max(case
                        when scantype_later_collect = '出库扫描'
                        then nvl(split(scantime_later_collect, ',')[1],split(scantime_early_collect, ',')[1])
                        else null end) as outstorage_scansitecode
              , min(case
                        when scantype_later_collect = '入库扫描'
                        then nvl(split(scantime_early_collect, ',')[0],split(scantime_later_collect, ',')[0])
                        else null end) as instorage_time
              , max(case
                        when scantype_later_collect = '出库扫描'
                        then nvl(split(scantime_later_collect, ',')[0],split(scantime_early_collect, ',')[0])
                        else null end) as outstorage_time
         from (
                  select billcode                                                       as waybill_no,             --    string comment    '运单编号',
                         scantype_later                                                 as scantype_later_collect, --    string    comment '扫描类型',
                         concat(substr(scantime_later, 1, 19), ',', scansitecode_later) as scantime_later_collect, --    timestamp    扫描时间
                         concat(substr(scantime_early, 1, 19), '', scansitecode_later)  as scantime_early_collect  --    timestamp    扫描时间
                  from jms_dwd.dwd_barscan_collect_dt
                  where dt <= '{{ execution_date | cst_ds }}' -- 取最近21天的数据
                    and dt >= date_add('{{ execution_date | cst_ds }}', -14)
                    and date_format(scantime_early, 'yyyy-MM-dd') <= date_add('{{ execution_date | cst_ds }}', 0)
                    and date_format(scantime_early, 'yyyy-MM-dd') >= date_add('{{ execution_date | cst_ds }}', -14)
                    and dimtype = 'billcode'
                    and scantype_later in ('入库扫描', '出库扫描')
              ) a
         group by waybill_no
     ),
     -- 签收
     sign as (
         select billcode                         as waybill_no,--    string    运单编号
                substr(delivertime_later, 1, 19) as delivertime_later, --    timestamp comment    '最晚派送时间',
                destsite_later, --string comment    '目的网点',
                -- realweight_later   --    decimal(10,2) comment    '实际重量',
                substr(inputtime_later, 1, 19)      inputtime_later --    timestamp    录入时间
         from jms_dwd.dwd_barscan_sign_dt
         where dt <= '{{ execution_date | cst_ds }}' -- 取最近30天的数据
           and dt >= date_add('{{ execution_date | cst_ds }}', -14)
           and date_format(delivertime_later, 'yyyy-MM-dd') <= date_add('{{ execution_date | cst_ds }}', 0)
           and date_format(delivertime_later, 'yyyy-MM-dd') >= date_add('{{ execution_date | cst_ds }}', -14)
           and dimtype = 'billcode'
     ),

     problem as (
         --一个问题件对应多个编码
         --（问题件编码,问题件状态，问题件扫描时间）
         -- 是否是问题件转作废件（问题件登记表中作废件建编码不为空且问题件处理状态是成功转为作废件 ）   运单中的is_void 是否一致
         -- 是否是问题件转投诉件（问题件中问题件编码为转为投诉件）  存在状态变更无法判断问题件是否转为投诉件再转为完结件
         -- 问题件表
         select waybill_no,
                collect_list(descs)  descs,
                max(problme_to_void) problme_to_void
         from (
                  select waybill_no,
                         named_struct('problme_type_code', second_level_type_code,
                                      'problme_scan_time', cast(scan_time as timestamp),
                                      'proble_piece_status', cast(proble_piece_status as int),
                                      'problem_finish_duration', cast(finish_duration as int)
                             ) as                                                descs,
                         if(is_blank_out = 1 and blank_out_no is not null, 1, 0) problme_to_void
                  from jms_ods.registration_problem_piece
                  where dt = '{{ execution_date | cst_ds }}'
              ) t
         group by waybill_no
     ),
     -- 退转拦截(已审核)
     reback as (
         select waybill_no,
                max(is_change_add)         is_change_add,
                max(change_add_apply_time) change_add_apply_time,
                max(is_reback_tak)         is_reback_tak,
                max(reback_tak_apply_time) reback_tak_apply_time
         from (
                  select waybill_no,                                                              --     string    订单号
                         apply_type_code,                                                         --     bigint    申请类型(5,4)
                         apply_type_name,                                                         --     string    申请类型名称(修改收件地址,退回揽收网点)
                         date_format(apply_time, 'yyyy-MM-dd HH:mm:ss') as apply_time,            --     timestamp    申请时间
                         if(apply_type_code = '5', 1, 0)                as is_change_add,         --是否修改收件地址 转寄
                         if(apply_type_code = '5', date_format(apply_time, 'yyyy-MM-dd HH:mm:ss'),
                            null)                                       as change_add_apply_time, --是否修改收件地址 转寄
                         if(apply_type_code = '4', 1, 0)                as is_reback_tak,--退回揽收网点 退回
                         if(apply_type_code = '4', date_format(apply_time, 'yyyy-MM-dd HH:mm:ss'),
                            null)                                       as reback_tak_apply_time  --退回揽收网点 退回
                  from jms_ods.tab_reback_transfer_express
                  where dt = '{{ execution_date | cst_ds }}'
                    and status = 2 --已审核
                    and (apply_type_code = 4 or apply_type_code = 5)
              ) t
         group by waybill_no
     ),
     -- 完结件
     ends as (
         select waybill_no,                             --    string    运单号
                end_code,                               --    string    完结类型编码
                end_code_desc,                          --    string    完结件编码描述
                source,                                 --    smallint    来源 1、问题件 2、质量工单 3、仲裁 4、客服工单
                substr(end_time, 1, 19)   as end_time,  --    timestamp    完结时间
                substr(input_time, 1, 19) as input_time --    timestamp    入库时间
         from jms_ods.tab_end_piece
         where dt = '{{ execution_date | cst_ds }}'
     ),
     fee as (
       select *
       from (
         select waybill_no,                                    --    string    运单号
                fee_type_code,                                 --    string    费用类型编码 底价发件费（S0320） 寄件提成账单(S0510)
                total_freight,                                 --    double    '客户总运费     取运单总运费'
                receive_pay_fee,                               --    double    到付费
                is_sign,                                       --    smallint    '签收标识     0: 未签收     1: 已签收'
                charge_weight,                                 --    double    '计费重量     默认取运单包裹内部计费重量'
                weight_source,                                 --    smallint    '重量来源     1:入仓重量 2:收件重量 3:内部计费重量 4:订单重量 5:批量刷新重量     默认为:1'
                settle_weight,                                 --    double    结算重量
                substr(delivery_time, 1, 19) as delivery_time, --    timestamp    寄件时间
                substr(sign_time, 1, 19)     as sign_time,     --    timestamp    签收时间
                quote_cal_type,                                --    smallint    账单报价算费类型
                bill_amount,                                    --    double    算费结果     账单金额
              row_number() over(partition by waybill_no order by update_time desc ) as rn
         from jms_ods.yl_oms_pick_bill
         where dt <= '{{ execution_date | cst_ds }}' -- 取最近30天的数据
           and dt >= date_add('{{ execution_date | cst_ds }}', -14)
           and date_format(input_time, 'yyyy-MM-dd') <= date_add('{{ execution_date | cst_ds }}', 0)
           and date_format(input_time, 'yyyy-MM-dd') >= date_add('{{ execution_date | cst_ds }}', -14)
   )temp
     where temp.rn=1

     )
insert overwrite table jms_dwd.dwd_wide_customer_detail_incre_dt partition ( dt )
-- 运单补充客户信息、网点信息、揽、发、派、收信息
select waybill.waybill_no
     , nvl(waybill.ordersource_code, orders.order_source_code)                                           as ordersource_code
     , nvl(waybill.ordersource_name, orders.order_source_name)                                           as ordersource_name
     , waybill.package_total_volume
     , waybill.package_volume
     , waybill.package_charge_weight
     , waybill.package_total_weight
     , waybill.package_inside_charge_weight
     , coalesce(taking.scantime_taking, warehouse.scantime_warehouse,
                waybill.collect_time,
                orders.pick_time)                                                                        as taking_time       -- 取件时间
     , coalesce(deliver.scantime_deliver, waybill.dispatch_time)                                         as dispatch_time
     , if(sign.waybill_no is not null or collect.outstorage_time is not null, 1, waybill.is_sign)        as is_sign
     , coalesce(waybill.sign_time, sign.delivertime_later, collect.outstorage_time)                      as sign_time
     , coalesce(deliver.scansitecode_deliver, waybill.dispatch_network_code,
                sign.destsite_later)                                                                     as dispatch_network_code
     , coalesce(waybill.dispatch_network_name, network.network_name)                                     as dispatch_network_name
     , waybill.exception_code
     , abnormal.name                                                                                     as exception_name
     , waybill.terminal_dispatch_code

     -- 问题件信息
     , waybill.is_abnormal
     --, problem.descs as problme_desc
     , problem.descs
     , problem.problme_to_void                                                                           as problme_to_void
     , waybill.is_void
     , waybill.is_refund
     , reback.is_change_add
     , reback.change_add_apply_time
     , reback.is_reback_tak
     , reback.reback_tak_apply_time

     -- 客户信息
     , waybill.customer_code
     , customer.customer_name
     , customer.customer_belong_network_id
     , customer.customer_belong_network_name
     , customer.create_time                                                                              as customer_signed_time
     , customer.customer_abbreviation
     , customer.customer_type_id
     , customer_type_name
     , customer.customer_country_id
     , customer.customer_country_code
     , customer.customer_province_id
     , customer.customer_city_id
     , customer.customer_area_id
     , customer.province_desc                                                                            as customer_province_desc
     , customer.city_desc                                                                                as customer_city_desc
     , customer.area_desc                                                                                as customer_area_desc
     , customer.customer_address
     , customer.nature_id                                                                                as customer_nature_id
     , customer.nature_id_name                                                                           as customer_nature_name
     , customer.settlement_id                                                                            as customer_settlement_id
     , customer.settlement_name                                                                          as customer_settlement_name
     , customer.pay_id                                                                                   as customer_pay_id
     , customer.pay_type_name                                                                            as customer_pay_type_name
     , customer.source_id                                                                                as customer_source_id
     , customer.meter_bubble                                                                             as customer_meter_bubble
     , customer.quote_id                                                                                 as customer_quote_id
     , customer.quote_name                                                                               as customer_quote_name
     , customer.account_period_id                                                                        as customer_account_period_id
     , customer.account_period_name                                                                      as customer_account_period_name
     , customer.attribute_id                                                                             as customer_attribute_id
     , customer.customer_attribute_name
     , customer.category_primary_id                                                                      as customer_category_primary_id
     , customer.category_primary_name                                                                    as customer_category_primary_name
     -- 运单
     , waybill.sender_province_id
     , waybill.sender_city_id
     , waybill.sender_area_id
     , waybill.sender_province_desc
     , waybill.sender_city_desc
     , waybill.sender_area_desc
     , coalesce(taking.scansitecode_taking, warehouse.scansitecode_warehouse,
                waybill.pick_network_code)                                                               as pick_network_code
     , coalesce(waybill.pick_network_name, taking.network_name,
                warehouse.network_name)                                                                  as pick_network_name

     , waybill.freight
     , waybill.total_freight
     , waybill.receiver_province_id
     , waybill.receiver_city_id
     , waybill.receiver_area_id
     , waybill.receiver_province_desc
     , waybill.receiver_city_desc
     , waybill.receiver_area_desc
     , waybill.is_business                                                                               as waybill_is_business
     , waybill.is_receive                                                                                as waybill_is_receive
     , nvl(waybill.goods_type_code, orders.goods_type_code)                                              as goods_type_code
     , waybill.goods_type_name
     -- 订单
     , customer_order_time                                                                                                    --    客户下单时间
     , cancel_time                                                                                       as order_cancel_time -- 订单取消时间
     , order_status_code                                                                                                      --  订单状态code
     , order_status_name
     , send.scantime_send
     , collect.instorage_time                                                                                                 -- 最早入库时间
     , collect.outstorage_time                                                                                                -- 最晚出库时间
     , if(ends.waybill_no is not null, 1, 0)                                                             as is_end            -- 是否完结
     , if(orders.waybill_id is not null, 1, 0)                                                           as is_exist_wayno    --运单表和订单表是否同时都有(1:存在运单和订单，0：只有运单，没有订单)
     , orders.order_input_time
     , if(collect.outstorage_time is not null or sign.waybill_no is not null, 1, 0)                         is_sign2
     , mall_id
     , mall_name
     , waybill.dt
from waybill -- 运单
         left join
     taking -- 揽收
     on waybill.waybill_no = taking.waybill_no
         left join
     send -- 发件
     on waybill.waybill_no = send.waybill_no
         left join
     warehouse -- 入仓表
     on waybill.waybill_no = warehouse.waybill_no
         left join
     deliver -- 派件
     on waybill.waybill_no = deliver.waybill_no
         left join
     collect -- 代收
     on waybill.waybill_no = collect.waybill_no
         left join
     sign -- 签收
     on waybill.waybill_no = sign.waybill_no
         left join
     customer
     on waybill.customer_code = customer.customer_code
         left join
     network -- 网点基本信息
     on waybill.pick_network_code = network.network_code
         left join
     problem -- 问题件
     on waybill.waybill_no = problem.waybill_no
         left join
     abnormal
     on waybill.exception_code = abnormal.code
         left join
     reback -- 退转拦截
     on waybill.waybill_no = reback.waybill_no
         left join
     ends -- 完结件
     on waybill.waybill_no = ends.waybill_no
         left join
     orders -- 订单
     on waybill.waybill_no = orders.waybill_id
         left join
     fee -- 寄件费用
     on waybill.waybill_no = fee.waybill_no
     distribute by dt, abs(hash(waybill.waybill_no)) % 60
;